﻿CREATE PROCEDURE Dir2Rss.uspGetRssXml
	@FolderGroupSk int
AS
BEGIN
	SET NOCOUNT ON;

WITH cteBase as (
	SELECT
		FolderGroupSk
		,FileGroupSk
		,FileGroupAdded
		,title			= FolderGroupName
		,link			= 'http://power.line.is/'
		,description	= [FolderGroupName]
		,language		= 'en-us'
		,copyright		= 'PowerLine ' + convert(varchar(4),YEAR(GETDATE()))
		,lastBuildDate	= convert(varchar,getdate(),113) + ' GMT'
		,category		= 'Fictional Educational Data'
		,generator		= 'http://Directory2Rss.codeplex.com/'
		,docs			= 'http://cyber.law.harvard.edu/rss/rss.html'
		,ttl			= 60

		,itemTitle			= 'Added to ' + FolderGroupName + ' at ' +left(convert(varchar,[FileGroupAdded],120),16)
	--	,itemlink			= 'http://power.line.is/'
		,itemdescription	= xmlDescription.description
		,itemguid			= [FileGroupGuid]
		,itempubDate		= convert(varchar,getdate(),113) + ' GMT'
	  FROM [Dir2Rss].[vFileGroup]
	CROSS APPLY (
		SELECT description = STUFF((
			SELECT
				' ' + CASE LEFT(REPLACE(FilePath,FolderGroupScanFolder,'') + '\' + FileName,1)
							WHEN '\' THEN REPLACE(FilePath,FolderGroupScanFolder,'') + '\' + FileName + '<br>'
							ELSE '\' + REPLACE(FilePath,FolderGroupScanFolder,'') + '\' + FileName + '<br>'
						END
			  FROM [Dir2Rss].[vFiles]
			WHERE FileGroupSk = [vFileGroup].FileGroupSk
			ORDER BY 1
		FOR XML PATH('')), 1, 1, '')
	) as xmlDescription
	WHERE xmlDescription.description is not null
)
SELECT 
RssXmlOutput = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
(SELECT TOP 1
	title
	,link
	,description
	,language
	,copyright
	,lastBuildDate
	,category
	,generator
	,docs
	,ttl
	,item = (
		SELECT
			title			= cteBaseItem.itemTitle
			,description	= cteBaseItem.itemdescription
			,guid			= cteBaseItem.itemguid
			,pubDate		= cteBaseItem.itempubDate
		  FROM cteBase cteBaseItem
		WHERE cteBase.FolderGroupSk = cteBaseItem.FolderGroupSk
		ORDER BY FileGroupAdded DESC
		FOR XML PATH('item'), TYPE
	)
  FROM cteBase
WHERE cteBase.FolderGroupSk = @FolderGroupSk
FOR XML PATH('channel'), ROOT('rss'))
,'&amp;gt;','&gt;')
,'&amp;lt;','&lt;')
,'<item><item>','<item>')
,'</item></item>','</item>')
,'<rss>','<?xml version="1.0" encoding="utf-8" standalone="yes"?><rss version="2.0">')

END
GO
